const express = require('express');
const router = express.Router();
const pool = require('../config/db');
const { auth, admin } = require('../middleware/auth');
const jwt = require('jsonwebtoken');
const bcrypt = require('bcryptjs');

router.get('/me', auth, async (req, res) => {
  try {
    const [users] = await pool.execute('SELECT id, username, role FROM user WHERE id = ?', [req.user.userId]);
    if (users.length === 0) {
      return res.status(404).json({ message: 'User not found' });
    }
    res.json(users[0]);
  } catch (error) {
    console.error(error);
    res.status(500).send('Server error');
  }
});

router.put('/me', auth, async (req, res) => {
  try {
    const { username, id, password } = req.body;
    const currentUserId = req.user.userId;
    if (id !== currentUserId) {
        const [existingUsers] = await pool.execute('SELECT * FROM user WHERE id = ? AND id != ?', [id, currentUserId]);
        if (existingUsers.length > 0) {
            return res.status(400).json({ message: 'User ID already taken' });
        }
    }
    const connection = await pool.getConnection();
    await connection.beginTransaction();

    try {
        let updateQuery = 'UPDATE user SET id = ?, username = ?';
        let updateParams = [id, username];

        if (password && password.trim()) {
            const hashedPassword = await bcrypt.hash(password, 10);
            updateQuery += ', password = ?';
            updateParams.push(hashedPassword);
        }

        updateQuery += ' WHERE id = ?';
        updateParams.push(currentUserId);

        await connection.execute(updateQuery, updateParams);

        if (id !== currentUserId) {
            await connection.execute(
                'UPDATE writer SET user_id = ? WHERE user_id = ?',
                [id, currentUserId]
            );
        }

        await connection.commit();
        connection.release();

        const token = jwt.sign(
          { userId: id, username: username, role: req.user.role },
          process.env.JWT_SECRET,
          { expiresIn: '24h' }
        );

        res.json({ message: 'User updated successfully', token });
    } catch (error) {
        await connection.rollback();
        connection.release();
        throw error;
    }
  } catch (error) {
    console.error(error);
    res.status(500).send('Server error');
  }
});

router.get('/writers', auth, async (req, res) => {
  try {
    const page = parseInt(req.query.page) || 1;
    const pageSize = parseInt(req.query.pageSize) || 8;
    const offset = (page - 1) * pageSize;

    const [writers] = await pool.execute(
      `SELECT * FROM writer WHERE user_id = ? OR user_id IS NULL LIMIT ${pageSize} OFFSET ${offset}`,
      [req.user.userId]
    );

    const [[{ total }]] = await pool.execute(
      'SELECT COUNT(*) as total FROM writer WHERE user_id = ? OR user_id IS NULL',
      [req.user.userId]
    );

    res.json({ writers, total });
  } catch (error) {
    console.error('Error fetching user writers:', error);
    res.status(500).json({ message: 'Failed to fetch writers' });
  }
});

router.get('/works', auth, async (req, res) => {
  try {
    const page = parseInt(req.query.page) || 1;
    const pageSize = parseInt(req.query.pageSize) || 10;
    const offset = (page - 1) * pageSize;

    const [works] = await pool.execute(`
      SELECT w.*, wr.name as writer_name, u.id as user_id, u.username as user_name
      FROM work w
      JOIN writer wr ON w.writer_id = wr.id
      JOIN user u ON wr.user_id = u.id
      WHERE u.id = ?
      LIMIT ${pageSize} OFFSET ${offset}
    `, [req.user.userId]);

    const [[{ total }]] = await pool.execute(`
      SELECT COUNT(*) as total 
      FROM work w
      JOIN writer wr ON w.writer_id = wr.id
      WHERE wr.user_id = ?
    `, [req.user.userId]);

    res.json({ works, total });
  } catch (error) {
    console.error('Error fetching user works:', error);
    res.status(500).json({ message: 'Failed to fetch works' });
  }
});

router.delete('/writers', [auth, admin], async (req, res) => {
    try {
        const { ids } = req.body;
        if (!ids || ids.length === 0) {
            return res.status(400).json({ message: 'No writer IDs provided' });
        }
        const [result] = await pool.query('DELETE FROM writer WHERE id IN (?)', [ids]);
        res.json({ message: `${result.affectedRows} writers deleted successfully.` });
    } catch (error) {
        console.error('Error deleting writers:', error);
        res.status(500).json({ message: 'Failed to delete writers' });
    }
});

router.delete('/works', [auth, admin], async (req, res) => {
    try {
        const { ids } = req.body;
        if (!ids || ids.length === 0) {
            return res.status(400).json({ message: 'No work IDs provided' });
        }
        const [result] = await pool.query('DELETE FROM work WHERE id IN (?)', [ids]);
        res.json({ message: `${result.affectedRows} works deleted successfully.` });
    } catch (error) {
        console.error('Error deleting works:', error);
        res.status(500).json({ message: 'Failed to delete works' });
    }
});

router.get('/admin/users', [auth, admin], async (req, res) => {
    try {
        const page = parseInt(req.query.page) || 1;
        const pageSize = parseInt(req.query.pageSize) || 8;
        const offset = (page - 1) * pageSize;
        const currentUserId = req.user.userId;

        const [users] = await pool.execute(
            `SELECT id, username, role, created_at FROM user WHERE id != ? LIMIT ${pageSize} OFFSET ${offset}`,
            [currentUserId]
        );

        const [[{ total }]] = await pool.execute(
            'SELECT COUNT(*) as total FROM user WHERE id != ?',
            [currentUserId]
        );

        res.json({ users, total });
    } catch (error) {
        console.error('Error fetching users:', error);
        res.status(500).json({ message: 'Failed to fetch users' });
    }
});

router.delete('/admin/users', [auth, admin], async (req, res) => {
    try {
        const { ids } = req.body;
        const currentUserId = req.user.userId;

        if (!ids || ids.length === 0) {
            return res.status(400).json({ message: 'No user IDs provided' });
        }
        if (ids.includes(currentUserId)) {
            return res.status(400).json({ message: 'Cannot delete yourself' });
        }

        const connection = await pool.getConnection();
        await connection.beginTransaction();

        try {
            // 使用参数化查询删除writer表中的相关记录
            const writerPlaceholders = ids.map(() => '?').join(',');
            await connection.execute(
                `DELETE FROM writer WHERE user_id IN (${writerPlaceholders})`,
                ids
            );
            
            // 使用参数化查询删除user表中的记录
            const userPlaceholders = ids.map(() => '?').join(',');
            const [result] = await connection.execute(
                `DELETE FROM user WHERE id IN (${userPlaceholders})`,
                ids
            );
            
            await connection.commit();
            connection.release();

            res.json({ message: `${result.affectedRows} users deleted successfully.` });
        } catch (error) {
            await connection.rollback();
            connection.release();
            throw error;
        }
    } catch (error) {
        console.error('Error deleting users:', error);
        res.status(500).json({ message: 'Failed to delete users' });
    }
});

router.put('/admin/users/:id', [auth, admin], async (req, res) => {
    try {
        const { id } = req.params;
        const { role } = req.body;
        const currentUserId = req.user.userId;

        if (id === currentUserId) {
            return res.status(400).json({ message: 'Cannot modify yourself' });
        }
        if (role !== 0 && role !== 1) {
            return res.status(400).json({ message: 'Invalid role value' });
        }
        await pool.execute(
            'UPDATE user SET role = ? WHERE id = ?',
            [role, id]
        );

        res.json({ message: 'User role updated successfully' });
    } catch (error) {
        console.error('Error updating user role:', error);
        res.status(500).json({ message: 'Failed to update user role' });
    }
});

router.get('/quotes', [auth, admin], async (req, res) => {
    try {
        const page = parseInt(req.query.page) || 1;
        const pageSize = parseInt(req.query.pageSize) || 8;
        const offset = (page - 1) * pageSize;

        const [quotes] = await pool.execute(`
            SELECT q.*, w.name as writer_name
            FROM quote q
            JOIN writer w ON q.writer_id = w.id
            ORDER BY q.created_at DESC
            LIMIT ${pageSize} OFFSET ${offset}
        `);

        const [[{ total }]] = await pool.execute('SELECT COUNT(*) as total FROM quote');

        res.json({ quotes, total });
    } catch (error) {
        console.error('Error fetching quotes:', error);
        res.status(500).json({ message: 'Failed to fetch quotes' });
    }
});

router.put('/quotes/:id', [auth, admin], async (req, res) => {
    try {
        const { id } = req.params;
        const { content, writer_name } = req.body;

        if (!content || content.trim() === '') {
            return res.status(400).json({ message: 'Quote content cannot be empty' });
        }

        if (!writer_name || writer_name.trim() === '') {
            return res.status(400).json({ message: 'Writer name cannot be empty' });
        }

        const connection = await pool.getConnection();
        await connection.beginTransaction();

        try {
            const [writers] = await connection.execute(
                'SELECT id FROM writer WHERE name = ?',
                [writer_name.trim()]
            );

            if (writers.length === 0) {
                await connection.rollback();
                connection.release();
                return res.status(400).json({ message: 'Writer not found' });
            }

            const writerId = writers[0].id;

            await connection.execute(
                'UPDATE quote SET content = ?, writer_id = ? WHERE id = ?',
                [content.trim(), writerId, id]
            );

            await connection.commit();
            connection.release();

            res.json({ message: 'Quote updated successfully' });
        } catch (error) {
            await connection.rollback();
            connection.release();
            throw error;
        }
    } catch (error) {
        console.error('Error updating quote:', error);
        res.status(500).json({ message: 'Failed to update quote' });
    }
});

router.delete('/quotes', [auth, admin], async (req, res) => {
    try {
        const { ids } = req.body;
        if (!ids || ids.length === 0) {
            return res.status(400).json({ message: 'No quote IDs provided' });
        }
        const [result] = await pool.query('DELETE FROM quote WHERE id IN (?)', [ids]);
        res.json({ message: `${result.affectedRows} quotes deleted successfully.` });
    } catch (error) {
        console.error('Error deleting quotes:', error);
        res.status(500).json({ message: 'Failed to delete quotes' });
    }
});

module.exports = router; 